﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.OleDb;

namespace PMS.DBUtility
{
    public class SQLHelper
    {
        //private readonly string strconn = "Provider=Microsoft.Jet.OleDb.4.0;Data Source=|DataDirectory|/Data/pms.mdb";
        private static readonly string strconn = "Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=true;Data Source=|DataDirectory|/Data/pms.mdb;Jet OLEDB:Database Password=123456;";
        private static OleDbConnection oleconn = null;
        private static OleDbCommand olecmd = null;
        public static bool open()
        {
            using(oleconn = new OleDbConnection(strconn))
            {
                if (oleconn.State == System.Data.ConnectionState.Closed)
                {
                    oleconn.Open();
                    return true;
                }
                return false;
            }
        }
        public static string State()
        {
            return oleconn.State.ToString();
        }
        /// <summary>
        /// 执行插入,删除,更新操作
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public static int  Execute(string strsql)
        {
            using (oleconn = new OleDbConnection(strconn))
            {
                oleconn.Open();
                using (olecmd = new OleDbCommand())
                {
                    olecmd.CommandType = System.Data.CommandType.Text;
                    olecmd.CommandText = strsql;
                    olecmd.Connection = oleconn;
                    return olecmd.ExecuteNonQuery();
                }
            }
        }
        public static int Execute(string strsql,OleDbParameter[] parameters)
        {
            using (oleconn = new OleDbConnection(strconn))
            {
                oleconn.Open();
                using (olecmd = new OleDbCommand(strsql,oleconn))
                {
                    for (int i = 0; i < parameters.Length; i++)
			        {
                        olecmd.Parameters.Add(parameters[i]);
			        }                
                    return olecmd.ExecuteNonQuery();
                }
            }            
        }
        /// <summary>
        /// 获取查询数据
        /// </summary>
        /// <param name="strsql"></param>
        /// <returns></returns>
        public static System.Data.DataSet GetData(string strsql)
        {
            using (oleconn = new OleDbConnection(strconn))
            {
                oleconn.Open();
                using (olecmd = new OleDbCommand(strsql,oleconn))
                {
                    OleDbDataAdapter oleDap = new OleDbDataAdapter(olecmd);
                    System.Data.DataSet ds = new System.Data.DataSet();
                    oleDap.Fill(ds);
                    return ds;

                }
            }
        }
    }
}
